/* $RCSFile: CrmFieldLocalizerDB.cs $
 * Apache license v2 2011 by Christian Surieux
 * $Source: ?/ADNCRM.Core/DAL/CrmFieldLocalizerDB.cs $
 * $log$
 * Revision 6 2010/08/06 19:56:28  christian.surieux
 *   Added Log to All cs files
 */

using System;
using System.Diagnostics;
using System.IO;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Globalization;

using AddonNice.Configuration;
using AddonNice.UI.WebControls;
using AddonNice.Diagnostics;

namespace AddonNice.Settings
{

	/// <summary>
	/// Class that encapsulates all data logic necessary to add/query/delete FiedlLocalizers within the Portal database.
	/// </summary>
	public class FieldLocalizerDB : DBDataStore
    {
        #region FieldLocalizerDesc
		
        public SqlDataReader GetFieldLocalizerDesc(int FieldId) 
        {
            SqlConnection theCon            =   newConnection;
            SqlCommand myCommand			=	new SqlCommand("dbo.adn_GetFieldLocalizerDesc", theCon);
            myCommand.CommandType			=	CommandType.StoredProcedure;

            SqlParameter parameterFieldId   =	new SqlParameter("@FieldId", SqlDbType.Int, 4);
            parameterFieldId.Value		    =	FieldId;
            myCommand.Parameters.Add(parameterFieldId);

            SqlDataReader result			=	null;
			try
			{
				theCon.Open();
				result						=	myCommand.ExecuteReader(CommandBehavior.CloseConnection);
			}
			catch(Exception exp)
			{
				if (DBTraceSwitch.Sw.TraceError)
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]FieldLocalizerDB GetFieldLocalizerDesc FieldId: {0}, ex: {1}",FieldId,exp),DBTraceSwitch.Sw.Info);
				if ( result != null )
					result.Close();
				if ( theCon.State == System.Data.ConnectionState.Open )
					theCon.Close();
				throw;
			}
			return result;
		}
        
        public SqlDataReader GetFieldLocalizerDescByPortal(int PortalId) 
        {
            SqlConnection theCon            =   newConnection;
            SqlCommand myCommand			=	new SqlCommand("dbo.adn_GetFieldLocalizerDescByPortal", theCon);
            myCommand.CommandType			=	CommandType.StoredProcedure;

            AddPortalIDParameters(myCommand,PortalId);

            SqlDataReader result			=	null;
			try
			{
				theCon.Open();
				result						=	myCommand.ExecuteReader(CommandBehavior.CloseConnection);
			}
			catch(Exception exp)
			{
				if (DBTraceSwitch.Sw.TraceError)
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]FieldLocalizerDB GetFieldLocalizerDescByPortal PortalId: {0}, ex: {1}",PortalId,exp),DBTraceSwitch.Sw.Info);
				if ( result != null )
					result.Close();
				if ( theCon.State == System.Data.ConnectionState.Open )
					theCon.Close();
				throw;
			}
			return result;
		}

        public SqlDataReader GetFieldLocalizerDescByEntity(int PortalId,string typeName) 
        {
            SqlConnection theCon            =   newConnection;
            SqlCommand myCommand			=	new SqlCommand("dbo.adn_GetFieldLocalizerDescByEntity", theCon);
            myCommand.CommandType			=	CommandType.StoredProcedure;

            AddPortalIDParameters(myCommand,PortalId);

            SqlParameter parametertypeName  =	new SqlParameter("@typeName", SqlDbType.NVarChar,100);
            parametertypeName.Value		    =	typeName;
            myCommand.Parameters.Add(parametertypeName);

            SqlDataReader result			=	null;
			try
			{
				theCon.Open();
				result						=	myCommand.ExecuteReader(CommandBehavior.CloseConnection);
			}
			catch(Exception exp)
			{
				if (DBTraceSwitch.Sw.TraceError)
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]FieldLocalizerDB GetFieldLocalizerDescByEntity PortalId: {0}, typeName: {1}, ex: {2}",PortalId,typeName,exp),DBTraceSwitch.Sw.Info);
				if ( result != null )
					result.Close();
				if ( theCon.State == System.Data.ConnectionState.Open )
					theCon.Close();
				throw;
			}
			return result;
		}

        public SqlDataReader GetFieldLocalizerDescByEntityField(int PortalId,string typeName,string fieldName) 
        {
            SqlConnection theCon            =   newConnection;
            SqlCommand myCommand			=	new SqlCommand("dbo.adn_GetFieldLocalizerDescByEntityField", theCon);
            myCommand.CommandType			=	CommandType.StoredProcedure;

            AddPortalIDParameters(myCommand,PortalId);

            SqlParameter parametertypeName  =	new SqlParameter("@typeName", SqlDbType.NVarChar,100);
            parametertypeName.Value		    =	typeName;
            myCommand.Parameters.Add(parametertypeName);

            SqlParameter parameterfieldName =	new SqlParameter("@fieldName", SqlDbType.NVarChar,100);
            parameterfieldName.Value		=	fieldName;
            myCommand.Parameters.Add(parameterfieldName);

            SqlDataReader result			=	null;
			try
			{
				theCon.Open();
				result						=	myCommand.ExecuteReader(CommandBehavior.CloseConnection);
			}
			catch(Exception exp)
			{
				if (DBTraceSwitch.Sw.TraceError)
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]FieldLocalizerDB GetFieldLocalizerDescByEntityField PortalId: {0}, typeName: {1}, fieldName: {2}, ex: {2}",
                        new object[]{PortalId,typeName,fieldName,exp}),DBTraceSwitch.Sw.Info);
				if ( result != null )
					result.Close();
				if ( theCon.State == System.Data.ConnectionState.Open )
					theCon.Close();
				throw;
			}
			return result;
		}

		/// <summary>
		/// </summary>
		public void DeleteFieldLocalizerDesc(int FieldId)
        {
            SqlConnection theCon            =   newConnection;
            SqlCommand myCommand            =   new SqlCommand("dbo.adn_DeleteFieldLocalizerDesc", theCon);
            myCommand.CommandType           =   CommandType.StoredProcedure;

            SqlParameter parameterFieldId   =	new SqlParameter("@FieldId", SqlDbType.Int, 4);
            parameterFieldId.Value		    =	FieldId;
            myCommand.Parameters.Add(parameterFieldId);

			try
			{
				theCon.Open();
				myCommand.ExecuteNonQuery();
			}
			catch(Exception exp)
			{
				if (DBTraceSwitch.Sw.TraceError)
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]FieldLocalizerDB DeleteFieldLocalizerDesc  FieldId: {0}, ex: {1}",FieldId, exp),DBTraceSwitch.Sw.Info);
				throw;
			}
			finally
			{
				if ( theCon.State == System.Data.ConnectionState.Open )
					theCon.Close();
			}
		}

	
        /// <summary>
        /// Do insert/ update, use FieldId=0 for insert
        /// </summary>
		public int UpdateFieldLocalizerDesc(int FieldId,int PortalId,string TypeName,string Fieldname,int FieldType,int FieldFlags,int FieldLenght,int DisplaySize) 
        {
            SqlConnection theCon                =   newConnection;
            SqlCommand myCommand			    =	new SqlCommand("dbo.adn_UpdateFieldLocalizerDesc", theCon);
            myCommand.CommandType			    =	CommandType.StoredProcedure;

            AddPortalIDParameters(myCommand,PortalId);

            SqlParameter parameterFieldId       =	new SqlParameter("@FieldId", SqlDbType.Int);
            parameterFieldId.Value		        =	FieldId;
            parameterFieldId.Direction          =    ParameterDirection.InputOutput;
            myCommand.Parameters.Add(parameterFieldId);

            SqlParameter parametertypeName      =	new SqlParameter("@TypeName", SqlDbType.NVarChar,100);
            parametertypeName.Value		        =	TypeName;
            myCommand.Parameters.Add(parametertypeName);

            SqlParameter parameterFieldname     =	new SqlParameter("@Fieldname", SqlDbType.NVarChar,100);
            parameterFieldname.Value	        =	Fieldname;
            myCommand.Parameters.Add(parameterFieldname);

            SqlParameter parameterFieldType     =	new SqlParameter("@FieldType", SqlDbType.Int);
            parameterFieldType.Value		    =	FieldType;
            myCommand.Parameters.Add(parameterFieldType);

            SqlParameter parameterFieldFlags    =	new SqlParameter("@FieldFlags", SqlDbType.Int);
            parameterFieldFlags.Value		    =	FieldFlags;
            myCommand.Parameters.Add(parameterFieldFlags);

            SqlParameter parameterFieldLenght   =	new SqlParameter("@FieldLenght", SqlDbType.Int);
            parameterFieldLenght.Value		    =	FieldLenght;
            myCommand.Parameters.Add(parameterFieldLenght);

            SqlParameter parameterDisplaySize   =	new SqlParameter("@DisplaySize", SqlDbType.Int);
            parameterDisplaySize.Value		    =	DisplaySize;
            myCommand.Parameters.Add(parameterDisplaySize);

			int ret							    =	-1;
			try
			{
				theCon.Open();
				myCommand.ExecuteNonQuery();
				ret							    =	(int)parameterFieldId.Value;
			}
			catch(Exception exp)
			{
				if (DBTraceSwitch.Sw.TraceError)
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]FieldLocalizerDB UpdateFieldLocalizerDesc FieldId: {0}, ex: {1}",FieldId,exp),DBTraceSwitch.Sw.Info);
				throw;
			}
			finally
			{
				if ( theCon.State == System.Data.ConnectionState.Open )
					theCon.Close();
			}
            return ret;
		}   

        #endregion FieldLocalizerDesc
        
        #region FieldLocalizer

        /// <summary>
        /// Get All localized fields for all a Poratl
        /// </summary>
		public SqlDataReader GetFieldLocalizerGlobalizedByPortal(int PortalId) 
        {
            SqlConnection theCon            =   newConnection;
            SqlCommand myCommand			=	new SqlCommand("dbo.adn_GetFieldLocalizerGlobalizedByPortal", theCon);
            myCommand.CommandType			=	CommandType.StoredProcedure;

            AddGlobalizeParameters(myCommand);
            AddPortalIDParameters(myCommand,PortalId);

            SqlDataReader result			=	null;
			try
			{
				theCon.Open();
				result						=	myCommand.ExecuteReader(CommandBehavior.CloseConnection);
			}
			catch(Exception exp)
			{
				if (DBTraceSwitch.Sw.TraceError)
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]FieldLocalizerDB GetFieldLocalizerGlobalizedByPortal PortalId: {0}, ex: {1}",PortalId,exp),DBTraceSwitch.Sw.Info);
				if ( result != null )
					result.Close();
				if ( theCon.State == System.Data.ConnectionState.Open )
					theCon.Close();
				throw;
			}
			return result;
		}

        /// <summary>
        /// Get All localized fields for all occurences of TypeName 
        /// </summary>
		public SqlDataReader GetFieldLocalizerGlobalizedByEntityAll(string TypeName) 
        {
            SqlConnection theCon            =   newConnection;
            SqlCommand myCommand			=	new SqlCommand("dbo.adn_GetFieldLocalizerGlobalizedByEntityAll", theCon);
            myCommand.CommandType			=	CommandType.StoredProcedure;

            AddGlobalizeParameters(myCommand);

            SqlParameter parametertypeName  =	new SqlParameter("@TypeName", SqlDbType.NVarChar,100);
            parametertypeName.Value		    =	TypeName;
            myCommand.Parameters.Add(parametertypeName);

            SqlDataReader result			=	null;
			try
			{
				theCon.Open();
				result						=	myCommand.ExecuteReader(CommandBehavior.CloseConnection);
			}
			catch(Exception exp)
			{
				if (DBTraceSwitch.Sw.TraceError)
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]FieldLocalizerDB GetFieldLocalizerGlobalizedByEntityAll TypeName: {0}, ex: {1}",TypeName,exp),DBTraceSwitch.Sw.Info);
				if ( result != null )
					result.Close();
				if ( theCon.State == System.Data.ConnectionState.Open )
					theCon.Close();
				throw;
			}
			return result;
		}

        /// <summary>
        /// Get All localized fields for one occurence of TypeName and CrmId
		/// </summary>
		public SqlDataReader GetFieldLocalizerGlobalizedByEntity(string TypeName,Guid @CrmID) 
        {
            SqlConnection theCon            =   newConnection;
            SqlCommand myCommand			=	new SqlCommand("dbo.adn_GetFieldLocalizerGlobalizedByEntity", theCon);
            myCommand.CommandType			=	CommandType.StoredProcedure;

            AddGlobalizeParameters(myCommand);

            SqlParameter parametertypeName  =	new SqlParameter("@TypeName", SqlDbType.NVarChar,100);
            parametertypeName.Value		    =	TypeName;
            myCommand.Parameters.Add(parametertypeName);

            SqlParameter parameterCrmID     =	new SqlParameter("@CrmID", SqlDbType.UniqueIdentifier);
            parameterCrmID.Value		    =	CrmID;
            myCommand.Parameters.Add(parameterCrmID);

            SqlDataReader result			=	null;
			try
			{
				theCon.Open();
				result						=	myCommand.ExecuteReader(CommandBehavior.CloseConnection);
			}
			catch(Exception exp)
			{
				if (DBTraceSwitch.Sw.TraceError)
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]FieldLocalizerDB GetFieldLocalizerGlobalizedByEntity TypeName: {0}, CrmID: {1}, ex: {2}",TypeName,CrmID,exp),DBTraceSwitch.Sw.Info);
				if ( result != null )
					result.Close();
				if ( theCon.State == System.Data.ConnectionState.Open )
					theCon.Close();
				throw;
			}
			return result;
		}


        /// <summary>
		/// </summary>
		public SqlDataReader GetSingleFieldLocalizerGlobalized(string TypeName,Guid CrmID,int FieldId) 
        {
            SqlConnection theCon            =   newConnection;
            SqlCommand myCommand			=	new SqlCommand("dbo.adn_GetSingleFieldLocalizerGlobalized", theCon);
            myCommand.CommandType			=	CommandType.StoredProcedure;

            AddGlobalizeParameters(myCommand);

            SqlParameter parametertypeName  =	new SqlParameter("@TypeName", SqlDbType.NVarChar,100);
            parametertypeName.Value		    =	TypeName;
            myCommand.Parameters.Add(parametertypeName);

            SqlParameter parameterCrmID     =	new SqlParameter("@CrmID", SqlDbType.UniqueIdentifier);
            parameterCrmID.Value		    =	CrmID;
            myCommand.Parameters.Add(parameterCrmID);

            SqlParameter parameterFieldId   =	new SqlParameter("@FieldId", SqlDbType.Int);
            parameterFieldId.Value		    =	FieldId;
            myCommand.Parameters.Add(parameterFieldId);

            SqlDataReader result			=	null;
			try
			{
				theCon.Open();
				result						=	myCommand.ExecuteReader(CommandBehavior.CloseConnection);
			}
			catch(Exception exp)
			{
				if (DBTraceSwitch.Sw.TraceError)
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]FieldLocalizerDB GetSingleFieldLocalizerGlobalized TypeName: {0}, CrmID: {1}, FieldId: {2}, ex: {2}",
                        new object[]{TypeName,CrmID,FieldId,exp}),DBTraceSwitch.Sw.Info);
				if ( result != null )
					result.Close();
				if ( theCon.State == System.Data.ConnectionState.Open )
					theCon.Close();
				throw;
			}
			return result;
		}

		public void UpdateFieldLocalizerGlobalized(string TypeName,Guid CrmID,int FieldId,string ShortValue,string LongValue) 
		{
            SqlConnection theCon                =   newConnection;
			SqlCommand myCommand				=   new SqlCommand("dbo.adn_UpdateFieldLocalizerGlobalized", theCon);
			myCommand.CommandType				=   CommandType.StoredProcedure;

			AddGlobalizeParameters(myCommand);

            SqlParameter parametertypeName      =	new SqlParameter("@TypeName", SqlDbType.NVarChar,100);
            parametertypeName.Value		        =	TypeName;
            myCommand.Parameters.Add(parametertypeName);

            SqlParameter parameterCrmID         =	new SqlParameter("@CrmID", SqlDbType.UniqueIdentifier);
            parameterCrmID.Value		        =	CrmID;
            myCommand.Parameters.Add(parameterCrmID);

            SqlParameter parameterFieldId       =	new SqlParameter("@FieldId", SqlDbType.Int);
            parameterFieldId.Value		        =	FieldId;
            myCommand.Parameters.Add(parameterFieldId);

            SqlParameter parameterShortValue    =	new SqlParameter("@ShortValue", SqlDbType.NVarChar,2000);
            parameterShortValue.Value		    =	ShortValue;
            myCommand.Parameters.Add(parameterShortValue);
            int len                             =   (string.IsNullOrEmpty(LongValue) ) ? 0:LongValue.Length;

            SqlParameter parameterLongValue     =	new SqlParameter("@LongValue", SqlDbType.NVarChar,len);
            parameterLongValue.Value		    =	LongValue;
            myCommand.Parameters.Add(parameterLongValue);

            try
			{
				theCon.Open();
				myCommand.ExecuteNonQuery();
			}
			catch(Exception exp)
			{
				if (DBTraceSwitch.Sw.TraceError)
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]FieldLocalizerDB UpdateFieldLocalizerGlobalized  FieldId: {0}, ex: {1}",FieldId,exp),DBTraceSwitch.Sw.Info);
				throw;
			}
			finally
			{
				if ( theCon.State == System.Data.ConnectionState.Open )
					theCon.Close();
			}
		}   

        public void DeleteFieldLocalizerGlobalized(string TypeName,Guid CrmID,int FieldId)
		{
            SqlConnection theCon                =   newConnection;
			SqlCommand myCommand				=   new SqlCommand("dbo.adn_DeleteFieldLocalizerGlobalized", theCon);
			myCommand.CommandType				=   CommandType.StoredProcedure;

            AddGlobalizeParameters(myCommand);

            SqlParameter parametertypeName      =	new SqlParameter("@TypeName", SqlDbType.NVarChar,100);
            parametertypeName.Value		        =	TypeName;
            myCommand.Parameters.Add(parametertypeName);

            SqlParameter parameterCrmID         =	new SqlParameter("@CrmID", SqlDbType.UniqueIdentifier);
            parameterCrmID.Value		        =	CrmID;
            myCommand.Parameters.Add(parameterCrmID);

            SqlParameter parameterFieldId       =	new SqlParameter("@FieldId", SqlDbType.Int);
            parameterFieldId.Value		        =	FieldId;
            myCommand.Parameters.Add(parameterFieldId);

			try
			{
				theCon.Open();
				myCommand.ExecuteNonQuery();
			}
			catch(Exception exp)
			{
				if (DBTraceSwitch.Sw.TraceError)
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]FieldLocalizerDB DeleteFieldLocalizerGlobalized TypeName: {0}, CrmID: {1}, FieldId: {2}, ex: {2}",
                        new object[]{TypeName,CrmID,FieldId,exp}),DBTraceSwitch.Sw.Info);
				throw;
			}
			finally
			{
				if ( theCon.State == System.Data.ConnectionState.Open )
					theCon.Close();
			}

		}
	
        #endregion FieldLocalizer
	}
}
